﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OPTFleet.OPTFleet;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data;

namespace OPTFleet.Data
{
    class GestaoPlaneamentos
    {
        /*
        private static String tabelaPlaneamentos = "planeamentos";
        private static String[] chavesPlaneamentos = { "data", "idfunc" };
        private static String[] camposPlaneamentos = { "idveiculo" };

        private static String tabelaEntregas = "entregas";
        private static String[] chavesEntregas = { "data", "idfunc", "hora" };
        private static String[] camposEntregas = { "idencomenda", "posx", "posy" };
        */

        private GestaoVeiculos gestaoVeiculos;
        private GestaoFuncionarios gestaoFuncionarios;
        private GestaoEncomendas gestaoEncomendas;
        private Conexao sqlConnection;
        public Conexao SqlConnection
        {
            set { sqlConnection = value; }
        }

        public GestaoPlaneamentos(GestaoVeiculos gestaoVeiculos,GestaoFuncionarios gestaoFuncionarios,GestaoEncomendas gestaoEncomendas)
        {
            this.sqlConnection = new Conexao();
            this.sqlConnection.connect();
            this.gestaoVeiculos = gestaoVeiculos;
            this.gestaoFuncionarios = gestaoFuncionarios;
            this.gestaoEncomendas = gestaoEncomendas;
        }

        public Planeamento ConsultarPlaneamento(int idfunc,DateTime data)
        {
            String stmtSql;
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            Planeamento p;
            SqlDataReader rs;
            int idveiculo;
            DateTime horachegada;
            DateTime dia = DateTime.Today;

            sqlCon = (SqlConnection)sqlConnection.Connection;

            /* SELECT idveiculo,horachegada
             * FROM planeamentos
             * WHERE data = @Data AND idfunc = @IDFunc
             */

            stmtSql = "SELECT idveiculo,horachegada FROM planeamentos WHERE data=@Data AND idfunc=@IDFunc";
            sqlCmd = new SqlCommand(stmtSql,sqlCon);
            sqlCmd.Parameters.Add("@Data",SqlDbType.SmallDateTime);
            sqlCmd.Parameters["@Data"].Value = dia;
            sqlCmd.Parameters.Add("@IDFunc",SqlDbType.Int);
            sqlCmd.Parameters["@IDFunc"].Value = idfunc;

            rs = sqlCmd.ExecuteReader();
            //idveiculo = (int)sqlCmd.ExecuteScalar();

            rs.Read();
            idveiculo = (int)rs["idveiculo"];
            horachegada = (DateTime)rs["horachegada"];

            rs.Close();

            p = new Planeamento();
            p.Data = dia;
            p.Motorista = (Motorista)gestaoFuncionarios.ConsultarFuncionario(idfunc);
            p.Veiculo = (Veiculo)gestaoVeiculos.ConsultarVeiculo(idveiculo);
            p.HoraChegada = horachegada;
            p.Entregas = GetEntregas(dia,idfunc);

            return p;
        }

        public List<Planeamento> ConsultarPlaneamentos(DateTime data)
        {
            String stmtSql;
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            Planeamento p;
            SqlDataReader rs;
            int idveiculo,idfunc;
            DateTime horachegada;
            DateTime dia = DateTime.Today;
            List<Planeamento> lst = new List<Planeamento>();

            sqlCon = new SqlConnection(sqlConnection.Connection.ConnectionString);
            sqlCon.Open();

            stmtSql = "SELECT idfunc,idveiculo,horachegada FROM planeamentos WHERE data=@Data";
            sqlCmd = new SqlCommand(stmtSql, sqlCon);
            sqlCmd.Parameters.Add("@Data", SqlDbType.SmallDateTime);
            sqlCmd.Parameters["@Data"].Value = dia;

            rs = sqlCmd.ExecuteReader();
            //idveiculo = (int)sqlCmd.ExecuteScalar();

            while (rs.Read())
            {
                idfunc = (int)rs["idfunc"];
                idveiculo = (int)rs["idveiculo"];
                horachegada = (DateTime)rs["horachegada"];

                p = new Planeamento();
                p.Data = dia;
                p.Motorista = (Motorista)gestaoFuncionarios.ConsultarFuncionario(idfunc);
                p.Veiculo = (Veiculo)gestaoVeiculos.ConsultarVeiculo(idveiculo);
                p.HoraChegada = horachegada;
                p.Entregas = GetEntregas(dia, idfunc);

                lst.Add(p);
            }

            rs.Close();
            sqlCon.Close();

            return lst;
        }

        public Boolean PlaneamentosCalculados(DateTime dia)
        {
            String stmtSql;
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            SqlDataReader rs;
            Boolean b;

            sqlCon = (SqlConnection)sqlConnection.Connection;

            stmtSql = "SELECT idfunc FROM planeamentos WHERE data=@Data";
            sqlCmd = new SqlCommand(stmtSql, sqlCon);
            sqlCmd.Parameters.Add("@Data", SqlDbType.SmallDateTime);
            sqlCmd.Parameters["@Data"].Value = dia;

            rs = sqlCmd.ExecuteReader();
            b = rs.Read();
            rs.Close();
            return b;
        }

        public void AlterarPlaneamento(Planeamento p)
        {
            throw new System.Exception("Not implemented");
        }

        private void InserirEmPlaneamentos(Planeamento p)
        {
            String stmtSql;
            SqlConnection sqlCon;
            SqlCommand sqlCmd;

            sqlCon = (SqlConnection)sqlConnection.Connection;

            stmtSql = "INSERT INTO planeamentos (data,idfunc,idveiculo,horachegada) VALUES(@Dia,@IDFunc,@IDVeic,@HoraChegada)";

            sqlCmd = new SqlCommand(stmtSql, sqlCon);
            sqlCmd.Parameters.Add("@Dia", SqlDbType.SmallDateTime);
            sqlCmd.Parameters["@Dia"].Value = p.Data;
            sqlCmd.Parameters.Add("@IDFunc", SqlDbType.Int);
            sqlCmd.Parameters["@IDFunc"].Value = p.Motorista.IDfuncionario;
            sqlCmd.Parameters.Add("@IDVeic", SqlDbType.Int);
            sqlCmd.Parameters["@IDVeic"].Value = p.Veiculo.IDveiculo;
            sqlCmd.Parameters.Add("@HoraChegada", SqlDbType.DateTime);
            sqlCmd.Parameters["@HoraChegada"].Value = p.HoraChegada;

            sqlCmd.ExecuteNonQuery();
        }

        private void InserirEmEntregas(Planeamento p)
        {
            String stmtSql;
            SqlConnection sqlCon;
            SqlCommand sqlCmd;

            sqlCon = (SqlConnection)sqlConnection.Connection;

            foreach(Entrega e in p.ShallowEntregas)
            {
                stmtSql = "INSERT INTO entregas (data,idfunc,hora,idencomenda,posx,posy) VALUES(@Dia,@IDFunc,@Hora,@IDEnc,@PosX,@PosY)";

                sqlCmd = new SqlCommand(stmtSql, sqlCon);
                sqlCmd.Parameters.Add("@Dia", SqlDbType.SmallDateTime);
                sqlCmd.Parameters["@Dia"].Value = p.Data;
                sqlCmd.Parameters.Add("@IDFunc", SqlDbType.Int);
                sqlCmd.Parameters["@IDFunc"].Value = p.Motorista.IDfuncionario;
                sqlCmd.Parameters.Add("@Hora", SqlDbType.SmallDateTime);
                sqlCmd.Parameters["@Hora"].Value = e.Hora;
                sqlCmd.Parameters.Add("@IDEnc", SqlDbType.Int);
                sqlCmd.Parameters["@IDEnc"].Value = e.Encomenda.IDencomenda;
                sqlCmd.Parameters.Add("@PosX", SqlDbType.Float);
                sqlCmd.Parameters["@PosX"].Value = e.PosX;
                sqlCmd.Parameters.Add("@PosY", SqlDbType.Float);
                sqlCmd.Parameters["@PosY"].Value = e.PosY;

                sqlCmd.ExecuteNonQuery();

                AtualizarEmEncomendas(e);
            }
        }

        private void AtualizarEmEncomendas(Entrega e)
        {
            String stmtSql;
            SqlConnection sqlCon;
            SqlCommand sqlCmd;

            sqlCon = (SqlConnection)sqlConnection.Connection;

            stmtSql = "UPDATE encomendas SET processada = @Data WHERE idencomenda = @IDEnc";

            sqlCmd = new SqlCommand(stmtSql, sqlCon);
            sqlCmd.Parameters.Add("@Data", SqlDbType.SmallDateTime);
            sqlCmd.Parameters["@Data"].Value = DateTime.Now;
            sqlCmd.Parameters.Add("@IDEnc", SqlDbType.Int);
            sqlCmd.Parameters["@IDEnc"].Value = e.Encomenda.IDencomenda;

            sqlCmd.ExecuteNonQuery();
        }

        public void SetPlaneamento(Planeamento p)
        {
            InserirEmPlaneamentos(p);
            InserirEmEntregas(p);
        }

        public void SetPlaneamentos(List<Planeamento> ps)
        {
            foreach(Planeamento p in ps)
            {
                SetPlaneamento(p);
            }
        }

        private Entrega MapEntrega(DbDataReader rs)
        {
            SqlDataReader sdr = (SqlDataReader)rs;
            Entrega e = new Entrega();
            int idencomenda;

            e.Hora = (DateTime)sdr["hora"];
            e.PosX = (Double)sdr["posx"];
            e.PosY = (Double)sdr["posy"];
            idencomenda = (int)sdr["idencomenda"];
            e.Encomenda = gestaoEncomendas.ConsultarEncomenda(idencomenda);

            return e;
        }

        private SortedSet<Entrega> GetEntregas(DateTime dia,int idfunc)
        {
            SortedSet<Entrega> ss = new SortedSet<Entrega>(new Entrega.OrdenarPorHora());
            String stmtSql;
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            SqlDataReader rs;
            Conexao con;

            con = new Conexao();
            con.connect();
            sqlCon = (SqlConnection) con.Connection;

            stmtSql = "SELECT hora,idencomenda,posx,posy FROM entregas WHERE data = @Dia AND idfunc = @IDFunc";
            sqlCmd = new SqlCommand(stmtSql, sqlCon);
            sqlCmd.Parameters.Add("@Dia", SqlDbType.SmallDateTime);
            sqlCmd.Parameters["@Dia"].Value = dia;
            sqlCmd.Parameters.Add("@IDFunc", SqlDbType.Int);
            sqlCmd.Parameters["@IDFunc"].Value = idfunc;

            rs = sqlCmd.ExecuteReader();

            while (rs.Read())
            {
                ss.Add(MapEntrega(rs));
            }


            rs.Close();
            con.disconnect();

            return ss;
        }

        public SortedSet<Int32> GetListPlaneamentos()
        {
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            String stmtSql;
            SqlDataReader rs;
            DateTime today = DateTime.Today;
            SortedSet<Int32> ss = new SortedSet<Int32>();

            sqlCon = (SqlConnection)sqlConnection.Connection;

            stmtSql = "SELECT idfunc FROM planeamentos WHERE data >= @Today";
            sqlCmd = new SqlCommand(stmtSql, sqlCon);
            sqlCmd.Parameters.Add("@Today", SqlDbType.SmallDateTime);
            sqlCmd.Parameters["@Today"].Value = today;

            rs = sqlCmd.ExecuteReader();

            while (rs.Read())
            {
                ss.Add((int)rs["idfunc"]);
            }

            rs.Close();
            return ss;
        }
    }

}